/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Formatter;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Duc Quyen
 */
public class Job {

    private String employerName;
    private int ms;
    private String title;
    private String amountPosition;
    private String description;
    private String level;
    private String skill;
    private String gender;
    private String experience;
    private String salaryRange;
    private String jobType;
    private Date deadLineApply;
    private String document;
    private String contactPerson;
    private int phoneNumber;
    private String emailForApply;
    private String province;
    private Date postDate;
    private Employer employer;
    private String branch;

    public Job() {
    }

    public Job(int ms, String title, String employer, String province, String salaryRange, Date postDate) {
        this.ms = ms;
        this.employerName = employer;
        this.title = title;
        this.province = province;
        this.salaryRange = salaryRange;
        this.postDate = postDate;
    }

    public Job getJobByID(int ID) {
        Job job = new Job();
        try {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement sta = con.prepareStatement("SELECT * from job WHERE job.JobID=?");
            sta.setInt(1, ID);
            ResultSet res = sta.executeQuery();
            PreparedStatement sta2 = con.prepareStatement("SELECT province.ProvinceName FROM "
                    + "job,workplace_job,province  where job.JobID=workplace_job.JobID "
                    + "and workplace_job.ProvinceID = province.ProvinceID  and job.JobID=?");
            sta2.setInt(1, ID);
            PreparedStatement sta3 = con.prepareStatement("SELECT branch.`Name` "
                    + "FROM job,branch_job,branch  where job.JobID=branch_job.JobID"
                    + " and branch_job.BranchID = branch.BranchID and job.JobID=?");
            sta3.setInt(1, ID);
            res.next();
            ms = res.getInt("JobID");
            int empID = res.getInt("EmployerID");
            System.out.println(empID);
            title = res.getString("Title");
            job.setTitle(title);
            Employer emp = new Employer();
            emp = emp.getEmployer(3);
            System.out.println(emp.getCompanyName());
            job.setEmployer(emp);
            amountPosition = res.getString("AmountPosition");
            job.setAmountPosition(amountPosition);
            description = res.getString("JobDescription");
            job.setDescription(description);
            level = res.getString("Level");
            job.setLevel(level);
            skill = res.getString("Skill");
            job.setSkill(skill);
            gender = res.getString("Gender");
            job.setGender(gender);
            experience = res.getString("Experience");
            job.setExperience(experience);
            salaryRange = res.getString("SalaryRange");
            job.setSalaryRange(salaryRange);
            jobType = res.getString("JobType");
            job.setJobType(jobType);
            deadLineApply = res.getDate("DeadLineApply");
            job.setDeadLineApply(deadLineApply);
            document = res.getString("JobDocument");
            job.setDocument(document);
            contactPerson = res.getString("ContactPerson");
            job.setContactPerson(contactPerson);
            phoneNumber = res.getInt("PhoneNumber");
            job.setPhoneNumber(phoneNumber);
            emailForApply = res.getString("EmailForApply");
            job.setEmailForApply(emailForApply);
            province = "";
            ResultSet res2 = sta2.executeQuery();
            while (res2.next()) {
                province = province + res2.getString(1) + "</br>";
            }
           
            job.setProvince(province);
            branch = "";
            ResultSet res3 = sta3.executeQuery();
            while (res3.next()) {
                branch = branch +  res3.getString(1) + "</br>";
            }
           
            job.setBranch(branch);

            con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }


        return job;
    }

    public ArrayList<Job> getJobByBranch(int ID) {
        ArrayList<Job> listJob = new ArrayList<Job>();
        Connection con = ConnectDatabase.getConnection();

        try {
            PreparedStatement sta1 = con.prepareStatement("SELECT job.JobID,job.Title,employer.CompanyName,SalaryRange,PostDate from job,employer "
                    + "where job.EmployerID=employer.EmployerID and job.BranchID=? order by job.PostDate");
            sta1.setInt(1, ID);
            ResultSet res1 = sta1.executeQuery();
            PreparedStatement sta2 = con.prepareStatement("SELECT province.ProvinceName FROM job,workplace_job,province  "
                    + "where job.JobID=workplace_job.JobID and workplace_job.ProvinceID = province.ProvinceID and job.BranchID=? and job.JobID=?");
            sta2.setInt(1, ID);

            while (res1.next()) {
                ms = res1.getInt("JobID");
                title = res1.getString("Title");
                employerName = res1.getString("CompanyName");
                salaryRange = res1.getString("SalaryRange");
                postDate = res1.getDate("PostDate");
                ms = res1.getInt("JobID");
                sta2.setInt(2, ms);
                ResultSet res2 = sta2.executeQuery();
                province = "<ul>";
                while (res2.next()) {
                    province = province + "<li>" + res2.getString(1) + "</li>";
                }
                province += "</ul>";
                Job job = new Job(ms, title, employerName, province, salaryRange, postDate);
                listJob.add(job);

            }
            con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return listJob;

    }

    public String getAmountPosition() {
        return amountPosition;
    }

    public void setAmountPosition(String amountPosition) {
        this.amountPosition = amountPosition;
    }

    public String getContactPerson() {
        return contactPerson;
    }

    public void setContactPerson(String contactPerson) {
        this.contactPerson = contactPerson;
    }

    public Date getDeadLineApply() {
        return deadLineApply;
    }

    public void setDeadLineApply(Date deadLineApply) {
        this.deadLineApply = deadLineApply;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getDocument() {
        return document;
    }

    public void setDocument(String document) {
        this.document = document;
    }

    public String getEmailForApply() {
        return emailForApply;
    }

    public void setEmailForApply(String emailForApply) {
        this.emailForApply = emailForApply;
    }

    public String getEmployerName() {
        return employerName;
    }

    public String getExperience() {
        return experience;
    }

    public void setExperience(String experience) {
        this.experience = experience;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getJobType() {
        return jobType;
    }

    public void setJobType(String jobType) {
        this.jobType = jobType;
    }

    public String getLevel() {
        return level;
    }

    public void setLevel(String level) {
        this.level = level;
    }

    public int getMs() {
        return ms;
    }

    public void setMs(int ms) {
        this.ms = ms;
    }

    public int getPhoneNumber() {
        return phoneNumber;
    }

    public void setPhoneNumber(int phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    public Date getPostDate() {
        return postDate;
    }

    public void setPostDate(Date postDate) {
        this.postDate = postDate;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getSalaryRange() {
        return salaryRange;
    }

    public void setSalaryRange(String salaryRange) {
        this.salaryRange = salaryRange;
    }

    public String getSkill() {
        return skill;
    }

    public void setSkill(String skill) {
        this.skill = skill;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String dateFormat(Date date) {
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        return formatter.format(date);

    }

    public Employer getEmployer() {
        return employer;
    }

    public void setEmployer(Employer employer) {
        this.employer = employer;
    }

    public String getBranch() {
        return branch;
    }

    public void setBranch(String branch) {
        this.branch = branch;
    }

//    public static void main(String[] args) {
//        Job j = new Job();
//        ArrayList<Job> list = j.getJobByBranch(3);
//        for (Job job : list) {
//            System.out.println(job.getTitle());
//        }
//
//    }
}
